﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO

ALTER PROCEDURE [dbo].[bhp_NemoGroupGet]
	@Id				INT
AS
BEGIN

	SELECT 
		t.id as Id,
		g.name as Name,
		t.name as SystemName,
		g.Identyficator,
		t.name as SelectTable,
		CASE t.typ WHEN 'T' THEN 'Table' WHEN 'V' THEN 'View' WHEN 'TE' THEN 'ExtendedTable' ELSE '' END as [Type]
	FROM 
		sTree t
		LEFT JOIN bht_Groups g ON t.id = g.Id
	WHERE
		(del IS NULL OR del = 0)
		AND typ IN ('V', 'T')
		AND parent = 0
		AND t.id = @Id

END
GO

CREATE TABLE [dbo].[bht_GroupTabs](
	[TabId] [int] IDENTITY(1,1) NOT NULL,
	[TabName] [nvarchar](200) NOT NULL,
	[Sort] [int] NOT NULL,
 CONSTRAINT [PK_bht_GroupTabs] PRIMARY KEY CLUSTERED 
(
	[TabId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[bht_GroupColumnsSplit](
	[TabId] [int] NOT NULL,
	[ColumnId] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[bht_GroupColumnsSplit]  WITH CHECK ADD  CONSTRAINT [FK_bht_GroupColumnsSplit_bht_Columns] FOREIGN KEY([ColumnId])
REFERENCES [dbo].[bht_Columns] ([ColumnId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[bht_GroupColumnsSplit] CHECK CONSTRAINT [FK_bht_GroupColumnsSplit_bht_Columns]
GO

ALTER TABLE [dbo].[bht_GroupColumnsSplit]  WITH CHECK ADD  CONSTRAINT [FK_bht_GroupColumnsSplit_bht_GroupTabs] FOREIGN KEY([TabId])
REFERENCES [dbo].[bht_GroupTabs] ([TabId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[bht_GroupColumnsSplit] CHECK CONSTRAINT [FK_bht_GroupColumnsSplit_bht_GroupTabs]
GO


CREATE PROCEDURE [dbo].[bhp_NemoGroupTabs]
	@GroupId	INT
AS
BEGIN

	
	SELECT DISTINCT
		tabs.*
	FROM
		sTree t
		INNER JOIN bht_GroupColumnsSplit sc ON t.id = sc.ColumnId
		INNER JOIN bht_GroupTabs tabs ON sc.TabId = tabs.TabId
	WHERE
		parent = @GroupId
		AND (t.del IS NULL OR t.del = 0)

END

GO

CREATE PROCEDURE [dbo].[bhp_NemoGroupColumnsSplit]
	@GroupId	INT
AS
BEGIN

	
	SELECT 
		sc.*
	FROM
		sTree t
		INNER JOIN bht_GroupColumnsSplit sc ON t.id = sc.ColumnId
		INNER JOIN bht_GroupTabs tabs ON sc.TabId = tabs.TabId
	WHERE
		parent = @GroupId
		AND (t.del IS NULL OR t.del = 0)

END